package dao;

import entity.StudentInfo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentInfoDao extends BaseDao {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    public List<StudentInfo> list(){
        //获取连接
        conn = super.getConnection();
        List<StudentInfo> list = new ArrayList<>();
        StudentInfo studentInfo = null;
        try{
            //SQL
            String sql = "select * from studentinfo order by sid desc";
            //执行SQL
            ps = conn.prepareStatement(sql);
            //结果集
            rs = ps.executeQuery();
            while (rs.next()){
                studentInfo = new StudentInfo();
                studentInfo.setSid(rs.getInt("sid"));
                studentInfo.setSname(rs.getString("sname"));
                studentInfo.setSgender(rs.getString("sgender"));
                studentInfo.setSage(rs.getInt("sage"));
                studentInfo.setSaddress(rs.getString("saddress"));
                studentInfo.setSemail(rs.getString("semail"));
                list.add(studentInfo);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            super.closeAll(conn,ps,rs);
        }
        return list;
    }

    public StudentInfo personal(int sid){
        //获取连接
        conn = super.getConnection();
        StudentInfo studentInfo = new StudentInfo();
        try{
            //SQL
            String sql = "select * from studentinfo where sid=?";
            //执行SQL
            ps = conn.prepareStatement(sql);
            ps.setInt(1,sid);
            rs = ps.executeQuery();
            if(rs.next()){
                studentInfo.setSid(rs.getInt("sid"));
                studentInfo.setSname(rs.getString("sname"));
                studentInfo.setSgender(rs.getString("sgender"));
                studentInfo.setSage(rs.getInt("sage"));
                studentInfo.setSaddress(rs.getString("saddress"));
                studentInfo.setSemail(rs.getString("semail"));
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            super.closeAll(conn,ps,rs);
        }
        return studentInfo;
    }

    public int update(StudentInfo studentInfo){
        //获取连接
        conn = super.getConnection();
        int count = 0;
        try{
            //SQL
            String sql = "update studentinfo set sname=?,sgender=?,sage=?,saddress=?,semail=? where sid=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,studentInfo.getSname());
            ps.setString(2,studentInfo.getSgender());
            ps.setInt(3,studentInfo.getSage());
            ps.setString(4,studentInfo.getSaddress());
            ps.setString(5,studentInfo.getSemail());
            ps.setInt(6,studentInfo.getSid());
            //执行SQL
            count = ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            super.closeAll(conn,ps,rs);
        }
        return count;
    }

    public int delete(int sid){
        //获取连接
        conn = super.getConnection();
        int count = 0;
        try{
            //SQL
            String sql = "delete from studentinfo where sid=?";
            //执行SQL
            ps = conn.prepareStatement(sql);
            ps.setInt(1,sid);
            count = ps.executeUpdate();
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            super.closeAll(conn,ps,rs);
        }
        return count;
    }
}
